package com.yulian.web_api.report;

import java.math.BigDecimal;
import java.util.*;

import com.healthmarketscience.sqlbuilder.Conditions;
import com.healthmarketscience.sqlbuilder.CustomSql;
import com.healthmarketscience.sqlbuilder.InCondition;
import com.healthmarketscience.sqlbuilder.SelectQuery;
import com.yulian.repository.CommonRepositoryImpl;
import com.yulian.util.CommonUtils;
import com.yulian.util.DataUtils;

import com.yulian.util.Pinyin4jUtil;
import com.yulian.util.TimeUtils;
import io.netty.util.internal.logging.InternalLogger;
import io.netty.util.internal.logging.Log4JLoggerFactory;
import io.vertx.core.CompositeFuture;
import io.vertx.core.Future;
import io.vertx.core.Promise;
import io.vertx.core.json.JsonArray;
import io.vertx.core.json.JsonObject;
import io.vertx.ext.auth.User;
import io.vertx.pgclient.PgPool;
import io.vertx.sqlclient.SqlConnection;
import io.vertx.sqlclient.Tuple;

/**
 * 该类位于业务逻辑层，主要实现干线运输支付管理报表数据查询，新增数据，更新数据，删除数据、导出、打印数据的业务逻辑，为MainVerticle中的REST接口提供服务
 *
 * @author
 */
public class MinlineTransportManager {
    final InternalLogger logger = Log4JLoggerFactory.getInstance(MinlineTransportManager.class);
    CommonRepositoryImpl commonRepository = new CommonRepositoryImpl();

    /**
     * 根据传入的查询参数获取满足条件的记录
     *
     * @param client
     * @param config
     * @param bodyJSObj
     * @return
     */
    public Future<JsonObject> getRecords(PgPool client, JsonObject config, JsonObject bodyJSObj) {
        Promise<JsonObject> promise = Promise.promise();
        //最后返回的JsonObject对象
        JsonObject responseJSObj = new JsonObject();
        // 验证必须传递的字段
        List<String> must_fields = Arrays.asList("legal_entity_id_list", "driver_name_list", "goods_gather_area_id_list", "batch_pickup_plan_load_completion_id_list", "mainline_route_id_list", "page_size", "page_index");
        JsonObject must = this.commonRepository.verifyAllParamMust(bodyJSObj, must_fields);
        if (must != null) {
            promise.complete(must);
            return promise.future();
        }
        //从bodyJson中获取数据
        String legal_entity_id_list = bodyJSObj.getString("legal_entity_id_list");
        String driver_name_list = bodyJSObj.getString("driver_name_list");
        String goods_gather_area_id_list = bodyJSObj.getString("goods_gather_area_id_list");
        String batch_pickup_plan_load_completion_id_list = bodyJSObj.getString("batch_pickup_plan_load_completion_id_list");
        String mainline_route_id_list = bodyJSObj.getString("mainline_route_id_list");
        String start_trade_date = bodyJSObj.getString("start_trade_date");
        String end_trade_date = bodyJSObj.getString("end_trade_date");

        int page_size = Integer.valueOf(bodyJSObj.getString("page_size"));
        int page_index = Integer.valueOf(bodyJSObj.getString("page_index"));
        int query_offset = (page_index - 1) * page_size;
        int query_limit = page_size;
        //数据是否为空校验
        if (DataUtils.isEmpty(legal_entity_id_list, driver_name_list, goods_gather_area_id_list, batch_pickup_plan_load_completion_id_list, mainline_route_id_list, page_size, page_index)) {
            promise.fail(CommonUtils.createExceptionJsonObjectStr(408, "参数 不合法"));
            return promise.future();
        }
        //用于传递给构建sql的JsonObject
        JsonObject queryJSObj = new JsonObject();
        queryJSObj.put("driver_name", driver_name_list);
        queryJSObj.put("legal_entity_id", legal_entity_id_list);
        queryJSObj.put("goods_gather_area_id", goods_gather_area_id_list);
        queryJSObj.put("batch_pickup_plan_load_completion_id", batch_pickup_plan_load_completion_id_list);
        queryJSObj.put("mainline_route_id", mainline_route_id_list);
        if (!DataUtils.isEmpty(start_trade_date)){
            queryJSObj.put("start_trade_date", start_trade_date);
        }
        if (!DataUtils.isEmpty(end_trade_date)){
            queryJSObj.put("end_trade_date", end_trade_date);
        }

        //定义多个set，存储每一个 mainline_transport 中多个code
        Set<String> legal_entity_id_list_set = new HashSet<String>();
        Set<String> truck_type_id_list_set = new HashSet<String>();
        Set<String> mainline_route_id_list_set = new HashSet<String>();
        Set<String> goods_gather_area_id_list_set = new HashSet<String>();
        Set<String> batch_pickup_plan_load_completion_id_list_set = new HashSet<String>();
        Set<String> site_id_list_set = new HashSet<String>();
        this.commonRepository.getCon(client).onSuccess(conn -> {
            String sql = createSelectSql(queryJSObj, query_offset, query_limit);
            this.commonRepository.queryJsonArray(conn, sql, "getMinlineTransportRecords")
            .onSuccess(resJSArray -> { //resJSArray中就是获取到符合条件的数据
                //        取出每条记录中含code字段的值放入对应的集合
                if(resJSArray.size()>0){
                    for (int i = 0; i < resJSArray.size(); i++) {
                        JsonObject jsonObj = resJSArray.getJsonObject(i);
                        legal_entity_id_list_set.add(jsonObj.getString("legal_entity_id"));
                        truck_type_id_list_set.add(jsonObj.getString("truck_type_id"));
                        mainline_route_id_list_set.add(jsonObj.getString("mainline_route_id"));
                        goods_gather_area_id_list_set.add(jsonObj.getString("goods_gather_area_id"));
                        batch_pickup_plan_load_completion_id_list_set.add(jsonObj.getString("batch_pickup_plan_load_completion_id"));
                        site_id_list_set.add(jsonObj.getString("site_id"));
                    }
                    queryJSObj.remove("start_trade_date");
                    queryJSObj.remove("end_trade_date");


                    //获取满足条件的记录总数
                    String count_sql = this.commonRepository.createCountSql(queryJSObj, "mainline_transport");
                    Future<Integer> getTotalFuture = this.commonRepository.getRecordsCount(conn, count_sql, "get_mainline_transport");
                    //获取运输商的编码和名称
                    String legal_entity_sql = "select distinct id, legal_entity_name from  payment_entity where id = any(array[" +
                            String.join(",", legal_entity_id_list_set) + "]) ";
                    Future<JsonObject> getLegalEntityFuture = this.commonRepository.getCodeNamePairJsonObject(conn, legal_entity_sql, "get_legal_entity_name");
                    //获取车型的编码和名称
                    String truck_type_sql = "select distinct id, type_name from  truck_type where id = any(array[" +
                            String.join(",", truck_type_id_list_set) + "]) ";
                    Future<JsonObject> getTruckTypeFuture = this.commonRepository.getCodeNamePairJsonObject(conn, truck_type_sql, "get_truck_type_name");
                    //获取干线运输标准线路编码和名称
                    String mainline_route_sql = "select distinct id,mainline_route_name from mainline_route where id = any(array[" +
                            String.join(",", mainline_route_id_list_set) + "]) and is_delete=0";
                    Future<JsonObject> getMainlineRouteFuture = this.commonRepository.getCodeNamePairJsonObject(conn, mainline_route_sql, "get_mainline_rout");
                    //获取供应链商品聚集区的编码和名称
                    String goods_gather_area_sql = "select distinct id, goods_gather_area_name from  goods_gather_area where id = any(array[" +
                            String.join(",", goods_gather_area_id_list_set) + "]) ";
                    Future<JsonObject> getGoodsGatherAreaFuture = this.commonRepository.getCodeNamePairJsonObject(conn, goods_gather_area_sql, "goods_gather_area_name");
                    //获取销售地区名称
                    String site_sql = "select distinct id, site_name from  site where id = any(array[" +
                            String.join(",", site_id_list_set) + "]) ";
                    Future<JsonObject> getSiteFuture = this.commonRepository.getCodeNamePairJsonObject(conn, site_sql, "get_truck_type_name");

                    //根据批次提货计划装车完工单id获取批次提货计划装车完工单编号
                    String batch_pickup_plan_load_completion_code_sql = "select id,batch_pickup_plan_load_completion_code from batch_pickup_plan_load_completion where id =  any(array[" + String.join(",", batch_pickup_plan_load_completion_id_list_set) + "]) and is_delete = 0";
                    Future<JsonObject> getBatchPickupPlanLoadCompletionCodeFuture = this.commonRepository.getCodeNamePairJsonObject(conn, batch_pickup_plan_load_completion_code_sql, "get_batch_pickup_plan_load_completion_code");

                    CompositeFuture.join(Arrays.asList(getTotalFuture, getLegalEntityFuture, getTruckTypeFuture, getMainlineRouteFuture, getGoodsGatherAreaFuture, getSiteFuture, getBatchPickupPlanLoadCompletionCodeFuture)).onComplete(ar2 -> {
                        if (ar2.succeeded()) {
                            JsonObject legal_entity_info = new JsonObject();
                            JsonObject truck_type_info = new JsonObject();
                            JsonObject mainline_route_info = new JsonObject();
                            JsonObject goods_gather_area_info = new JsonObject();
                            JsonObject site_info = new JsonObject();
                            JsonObject batch_pickup_plan_load_completion_code_info = new JsonObject();

                            int total_records = Integer.valueOf(ar2.result().list().get(0).toString());
                            legal_entity_info = (JsonObject) ar2.result().list().get(1);
                            truck_type_info = (JsonObject) ar2.result().list().get(2);
                            mainline_route_info = (JsonObject) ar2.result().list().get(3);
                            goods_gather_area_info = (JsonObject) ar2.result().list().get(4);
                            site_info = (JsonObject) ar2.result().list().get(5);
                            batch_pickup_plan_load_completion_code_info = (JsonObject) ar2.result().list().get(6);


                            // 最后组装含有name的Json对象和数组
                            JsonArray recordsJSArray = new JsonArray();
                            for (int i = 0; i < resJSArray.size(); i++) {
                                JsonObject record_obj = resJSArray.getJsonObject(i);
                                String legal_entity_id = record_obj.getString("legal_entity_id");
                                record_obj.put("legal_entity_name", legal_entity_info.getString(legal_entity_id));
                                String truck_type_id = record_obj.getString("truck_type_id");
                                record_obj.put("truck_type_name", truck_type_info.getString(truck_type_id));
                                String mainline_route_id = record_obj.getString("mainline_route_id");
                                record_obj.put("mainline_route_name", mainline_route_info.getString(mainline_route_id));
                                String goods_gather_area_id = record_obj.getString("goods_gather_area_id");
                                record_obj.put("goods_gather_area_name", goods_gather_area_info.getString(goods_gather_area_id));
                                String site_id = record_obj.getString("site_id");
                                record_obj.put("site_name", site_info.getString(site_id));
                                String batch_pickup_plan_load_completion_id = record_obj.getString("batch_pickup_plan_load_completion_id");
                                record_obj.put("batch_pickup_plan_load_completion_code", batch_pickup_plan_load_completion_code_info.getString(batch_pickup_plan_load_completion_id));
                                //应付干线运费金额
                                BigDecimal bigDecimal1 = new BigDecimal(Double.parseDouble(record_obj.getString("freight_amount")==null? "0":record_obj.getString("freight_amount")));
                                BigDecimal bigDecimal2 = new BigDecimal(Double.parseDouble(record_obj.getString("goods_loss_compensation_amount")==null? "0":record_obj.getString("goods_loss_compensation_amount")));
                                record_obj.put("pay_mainline_freight_amount", bigDecimal1.subtract(bigDecimal2).doubleValue());
                                //税额
                                BigDecimal bigDecimal3 = new BigDecimal(Double.parseDouble(record_obj.getString("tax_rate")==null? "0":record_obj.getString("tax_rate")));
                                BigDecimal bigDecimal4 = new BigDecimal(Double.parseDouble(record_obj.getString("pay_mainline_freight_amount")==null? "0":record_obj.getString("pay_mainline_freight_amount")));
                                record_obj.put("tax_amount", bigDecimal3.multiply(bigDecimal4).doubleValue());
                                //运输管理费收入
                                BigDecimal bigDecimal5 = new BigDecimal(Double.parseDouble(record_obj.getString("transport_management_rate")==null? "0":record_obj.getString("transport_management_rate")));
                                BigDecimal bigDecimal6 = new BigDecimal(Double.parseDouble(record_obj.getString("pay_mainline_freight_amount")==null? "0":record_obj.getString("pay_mainline_freight_amount")));
                                record_obj.put("transport_management_amount", bigDecimal5.multiply(bigDecimal6).doubleValue());
                                recordsJSArray.add(record_obj);
                            }
                            JsonObject dataJSObj = new JsonObject();
                            dataJSObj.put("total", total_records).put("records", recordsJSArray);
                            responseJSObj.put("code", 200).put("msg", "获取记录成功").put("data", dataJSObj);

                            promise.complete(responseJSObj);
                        } else {
                            logger.info("获取记录数据发生错误: {}", ar2.cause().getMessage());
                            promise.complete(CommonUtils.createExceptionJsonObject(ar2, "getMinlineTransportRecords", "获取干线运输支付管理用失败"));
                        }
                    });
                }else {
                    JsonObject dataJSObj = new JsonObject();
                    dataJSObj.put("total", 0).put("records", "");
                    responseJSObj.put("code", 200).put("msg", "获取记录成功").put("data", dataJSObj);
                    promise.complete(responseJSObj);
                }
            }).onFailure(err -> promise.complete(CommonUtils.createExceptionJsonObject(err, "getMinlineTransportRecords", "获取干线运输支付管理用失败")));
        }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getMinlineTransportRecords", "数据库连接失败")));

        return promise.future();
    }

    /**
     * 查看每条数据对应的批次提货计划装车完工单到岸价明细
     *
     * @param client
     * @param config
     * @param bodyJSObj
     * @return
     */
    public Future<JsonObject> getRecordsForDetails(PgPool client, JsonObject config, JsonObject bodyJSObj) {
        Promise<JsonObject> promise = Promise.promise();
        //最后返回的JsonObject对象
        JsonObject responseJSObj = new JsonObject();
        // 验证必须传递的字段
        List<String> must_fields = Arrays.asList("goods_gather_area_id", "site_id", "batch_pickup_plan_load_completion_id", "real_site_mainline_freight_price", "page_size", "page_index");
        JsonObject must = this.commonRepository.verifyAllParamMust(bodyJSObj, must_fields);
        if (must != null) {
            promise.complete(must);
            return promise.future();
        }
        //从bodyJson中获取数据
        String goods_gather_area_id = bodyJSObj.getString("goods_gather_area_id");
        String site_id = bodyJSObj.getString("site_id");
        String batch_pickup_plan_load_completion_id = bodyJSObj.getString("batch_pickup_plan_load_completion_id");
        double real_site_mainline_freight_price = Double.parseDouble(bodyJSObj.getString("real_site_mainline_freight_price"));
        int page_size = Integer.valueOf(bodyJSObj.getString("page_size"));
        int page_index = Integer.valueOf(bodyJSObj.getString("page_index"));
        int query_offset = (page_index - 1) * page_size;
        int query_limit = page_size;
        //数据是否为空校验
        if (DataUtils.isEmpty(goods_gather_area_id, site_id, batch_pickup_plan_load_completion_id, real_site_mainline_freight_price, page_size, page_index)) {
            promise.fail(CommonUtils.createExceptionJsonObjectStr(408, "参数 不合法"));
            return promise.future();
        }

        //用于传递给构建sql的JsonObject
        JsonObject queryJSObj = new JsonObject();
        queryJSObj.put("batch_pickup_plan_load_completion_id", batch_pickup_plan_load_completion_id);


        //定义多个set，存储从 batch_pickup_plan_load_completion_detail 获取到的多个code
        Set<String> supply_chain_goods_id_list_set = new HashSet<String>();
        Set<String> real_supply_chain_goods_grade_id_list_set = new HashSet<String>();
        Set<String> supplier_pickup_plan_payment_detail_id_list_set = new HashSet<String>();

        this.commonRepository.getCon(client).onSuccess(conn -> {
            String sql = createSelectSqlForDetail(queryJSObj, query_offset, query_limit);
            this.commonRepository.queryJsonArray(conn, sql, "getBatchPickupPlanLoadCompletionDetailRecords")
            .onSuccess(resJSArray -> { //resJSArray中就是获取到符合条件的数据
                //        取出每条记录中含code字段的值放入对应的集合
                String supplier_pickup_plan_payment_detail_id_str="";
                if (resJSArray.size()==0){
                    //没有数据
                    JsonObject dataJSObj = new JsonObject();
                    dataJSObj.put("total", 0).put("records", "[]");
                    responseJSObj.put("code", 200).put("msg", "获取记录成功").put("data", dataJSObj);
                    promise.complete(responseJSObj);
                }else {
                    for (int i = 0; i < resJSArray.size(); i++) {
                        JsonObject jsonObj = resJSArray.getJsonObject(i);
                        supply_chain_goods_id_list_set.add(jsonObj.getString("supply_chain_goods_id"));
                        real_supply_chain_goods_grade_id_list_set.add(jsonObj.getString("real_supply_chain_goods_grade_id"));
                        supplier_pickup_plan_payment_detail_id_list_set.add(jsonObj.getString("supplier_pickup_plan_payment_detail_id"));
                        if (i==resJSArray.size()-1){
                            supplier_pickup_plan_payment_detail_id_str=supplier_pickup_plan_payment_detail_id_str+jsonObj.getString("supplier_pickup_plan_payment_detail_id");
                        }else {
                            supplier_pickup_plan_payment_detail_id_str=supplier_pickup_plan_payment_detail_id_str+jsonObj.getString("supplier_pickup_plan_payment_detail_id")+",";
                        }
                    }

                    //根据获取到的supplier_pickup_plan_payment_detail_id从供应商提货计划支付明细单中获取供应链商品名、实际结算包规、结算斤价
                    String supplier_pickup_plan_payment_detail_sql = "select distinct supply_chain_goods_id,real_settlement_package_spec,settlement_price from  supplier_pickup_plan_payment_detail where id in ("+supplier_pickup_plan_payment_detail_id_str+" ) ";
                    this.commonRepository.queryJsonArray(conn, supplier_pickup_plan_payment_detail_sql, "get_supplier_pickup_plan_payment_detai").onSuccess(resJSArray1 -> {
                        JsonObject real_settlement_package_spec_info = new JsonObject();
                        JsonObject settlement_price_info = new JsonObject();

                        for (int i = 0; i < resJSArray1.size(); i++) {
                            JsonObject jsonObj = resJSArray1.getJsonObject(i);
                            real_settlement_package_spec_info.put(jsonObj.getString("supply_chain_goods_id"), jsonObj.getString("real_settlement_package_spec"));
                            settlement_price_info.put(jsonObj.getString("supply_chain_goods_id"), jsonObj.getString("settlement_price"));
                        }
                        //获取满足条件的记录总数
                        String count_sql = this.commonRepository.createCountSql(queryJSObj, "batch_pickup_plan_load_completion_detail");
                        Future<Integer> getTotalFuture = this.commonRepository.getRecordsCount(conn, count_sql, "get_mainline_transport");
                        //获取供应链商品名的编码和名称
                        String supply_chain_goods_sql = "select distinct id, supply_chain_goods_name from  supply_chain_goods where id = any(array[" +
                                String.join(",", supply_chain_goods_id_list_set) + "]) ";
                        Future<JsonObject> getSupplyChainGoodsFuture = this.commonRepository.getCodeNamePairJsonObject(conn, supply_chain_goods_sql, "get_supply_chain_goods_name");
                        //获取实际供应链商品等级的编码和名称
                        String real_supply_chain_goods_grade_sql = "select distinct id, grade_name from  goods_grade where id = any(array[" +
                                String.join(",", real_supply_chain_goods_grade_id_list_set) + "])";
                        Future<JsonObject> getRealSupplyChainGoodsGradeFuture = this.commonRepository.getCodeNamePairJsonObject(conn, real_supply_chain_goods_grade_sql, "get_real_supply_chain_goods_grade_name");
                        //获取供应链商品聚集区编码和名称
                        String goods_gather_area_sql = "select distinct id, goods_gather_area_name from  goods_gather_area where id = $1";
                        Future<JsonObject> getGoodsGatherAreaFuture = this.commonRepository.queryOneJsonObjectThrow(conn, goods_gather_area_sql, Tuple.of(Long.parseLong(goods_gather_area_id)), "get_goods_gather_area_name", "获取供应链商品聚集区");
                        //获取供应链商品聚集区编码和名称
                        String site_sql = "select distinct id, site_name from  site where id = $1";
                        Future<JsonObject> getSiteFuture = this.commonRepository.queryOneJsonObjectThrow(conn, site_sql, Tuple.of(Long.parseLong(site_id)), "get_site_name", "获取销售地区");

                        CompositeFuture.join(Arrays.asList(getTotalFuture, getSupplyChainGoodsFuture, getRealSupplyChainGoodsGradeFuture, getGoodsGatherAreaFuture, getSiteFuture)).onComplete(ar2 -> {
                            if (ar2.succeeded()) {
                                JsonObject supply_chain_goods_info = new JsonObject();
                                JsonObject real_supply_chain_goods_grade_info = new JsonObject();
                                JsonObject goods_gather_area_info = new JsonObject();
                                JsonObject site_info = new JsonObject();

                                int total_records = Integer.valueOf(ar2.result().list().get(0).toString());
                                supply_chain_goods_info = (JsonObject) ar2.result().list().get(1);
                                real_supply_chain_goods_grade_info = (JsonObject) ar2.result().list().get(2);
                                goods_gather_area_info = (JsonObject) ar2.result().list().get(3);
                                site_info = (JsonObject) ar2.result().list().get(4);

                                // 最后组装含有name的Json对象和数组
                                JsonArray recordsJSArray = new JsonArray();
                                for (int i = 0; i < resJSArray.size(); i++) {
                                    JsonObject record_obj = resJSArray.getJsonObject(i);
                                    //供应链商品聚集区
                                    record_obj.put("goods_gather_area_name", goods_gather_area_info.getString("goods_gather_area_name"));
                                    //销售地区
                                    record_obj.put("site_name", site_info.getString("site_name"));
                                    //供应链商品名
                                    String supply_chain_goods_id = record_obj.getString("supply_chain_goods_id");

                                    record_obj.put("supply_chain_goods_name", supply_chain_goods_info.getString(supply_chain_goods_id));
                                    //实际供应链商品等级

                                    String real_supply_chain_goods_grade_id = record_obj.getString("real_supply_chain_goods_grade_id");
                                    record_obj.put("real_supply_chain_goods_grade_name", real_supply_chain_goods_grade_info.getString(real_supply_chain_goods_grade_id));
                                    //实际结算包规
                                    record_obj.put("real_settlement_package_spec", Double.parseDouble(real_settlement_package_spec_info.getString(supply_chain_goods_id)));
                                    //实际结算斤价
                                    record_obj.put("settlement_price", Double.parseDouble(settlement_price_info.getString(supply_chain_goods_id)));
                                    //销售地区实际运费斤价
                                    record_obj.put("real_site_mainline_freight_price", real_site_mainline_freight_price);
                                    //实际到岸斤价
                                    record_obj.put("pay_mainline_freight_amount", record_obj.getDouble("settlement_price") + real_site_mainline_freight_price);
                                    recordsJSArray.add(record_obj);
                                }
                                JsonObject dataJSObj = new JsonObject();
                                dataJSObj.put("total", total_records).put("records", recordsJSArray);
                                responseJSObj.put("code", 200).put("msg", "获取记录成功").put("data", dataJSObj);
                                promise.complete(responseJSObj);
                            } else {
                                logger.info("获取记录数据发生错误: {}", ar2.cause().getMessage());
                                promise.complete(CommonUtils.createExceptionJsonObject(ar2, "getMinlineTransportRecords", "获取干线运输支付管理用失败"));
                            }
                        });
                    }).onFailure(err -> promise.complete(CommonUtils.createExceptionJsonObject(err, "getMinlineTransportRecords", "获取供应链商品名实际结算包规结算斤价失败")));

                }
      }).onFailure(err -> promise.complete(CommonUtils.createExceptionJsonObject(err, "getMinlineTransportRecords", "获取干线运输支付管理用失败")));
        }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getMinlineTransportRecords", "数据库连接失败")));

        return promise.future();
    }


    /**
     * 创建查询符合搜索条件记录的sql语句
     *
     * @param queryJS      body参数对象
     * @param query_offset 页面偏移
     * @param query_limit  页面记录数
     * @return 查询符合搜索条件的sql语句
     */
    public String createSelectSql(JsonObject queryJS, int query_offset, int query_limit) {
        SelectQuery sq = new SelectQuery();
        sq.addCustomColumns(new CustomSql("id"), new CustomSql("legal_entity_id"), new CustomSql("driver_name"), new CustomSql("truck_type_id"),
        new CustomSql("plate_number"), new CustomSql("mainline_transport_document_code"),
        new CustomSql("mainline_route_id"), new CustomSql("freight_amount"), new CustomSql("goods_gather_area_id"), new CustomSql("site_id"), new CustomSql("freight_allocation_ratio"),
        new CustomSql("batch_pickup_plan_load_completion_id"), new CustomSql("goods_loss_compensation_amount"), new CustomSql("tax_rate"), new CustomSql("transport_management_rate"),
        new CustomSql("is_delete"), new CustomSql("create_user"), new CustomSql("update_user"), new CustomSql("create_time"), new CustomSql("update_time"),
        new CustomSql("sales_pickup_number"), new CustomSql("sales_pickup_weight"), new CustomSql("sales_real_transport_price"))
        .addCustomFromTable("mainline_transport");
//        sq.getWhereClause().addCustomCondition(Conditions.greaterThanOrEq(new CustomSql("create_time"), TimeUtils.parseLocalDate(queryJS.getString("start_trade_date"), "yyyy-MM-dd")));
//        sq.getWhereClause().addCustomCondition(Conditions.lessThanOrEq(new CustomSql("create_time"), TimeUtils.parseLocalDate(queryJS.getString("end_trade_date"), "yyyy-MM-dd")));
        Iterator<String> iter = queryJS.fieldNames().iterator();
        while (iter.hasNext()) {
            String key = iter.next();
            try {
                String value = queryJS.getString(key);
                if ("start_trade_date".equals(key)) {
                    sq.getWhereClause().addCustomCondition(Conditions.greaterThanOrEq(new CustomSql("create_time"),
                    TimeUtils.parseLocalDate(value, "yyyy-MM-dd")).setDisableParens(true));
                    continue;
                } else if ("end_trade_date".equals(key)) {
                    sq.getWhereClause().addCustomCondition(Conditions.lessThanOrEq(new CustomSql("create_time"),
                    TimeUtils.parseLocalDate(value, "yyyy-MM-dd")).setDisableParens(true));
                    continue;
                }
                if (!value.equals("") && !value.equals("[]") && !value.equals("[all]")) {
                    String firstChar = value.substring(0, 1);
                    if (firstChar.equals("["))
                        value = value.substring(1, value.length() - 1);
                    value = value.replace(", ", "','");
                    // 添加where InCondtion子句
                    sq.getWhereClause().addCustomCondition(new InCondition(new CustomSql(key), value));
                }
            } catch (Exception e) {
                logger.info("createSql, 发生错误,错误原因: ", e.getMessage());
            }
        }
        return sq.validate() + " order by id desc limit " + query_limit + " offset " + query_offset;
    }

    public String createSelectSqlForDetail(JsonObject queryJS, int query_offset, int query_limit) {
        SelectQuery sq = new SelectQuery();
        sq.addCustomColumns(new CustomSql("id"), new CustomSql("supply_chain_goods_id"), new CustomSql("supplier_pickup_plan_payment_detail_id"), new CustomSql("real_supply_chain_goods_grade_id"),
        new CustomSql("is_delete"), new CustomSql("create_user"), new CustomSql("update_user"), new CustomSql("create_time"), new CustomSql("update_time"))
        .addCustomFromTable("batch_pickup_plan_load_completion_detail");
        Iterator<String> iter = queryJS.fieldNames().iterator();
        while (iter.hasNext()) {
            String key = iter.next();
            try {
                String value = queryJS.getString(key);
                if (!value.equals("") && !value.equals("[]") && !value.equals("[all]")) {
                    String firstChar = value.substring(0, 1);
                    if (firstChar.equals("["))
                        value = value.substring(1, value.length() - 1);
                    value = value.replace(", ", ",");
                    // 添加where  InCondtion 子句
                    sq.getWhereClause().addCustomCondition(new InCondition(new CustomSql(key), value));
                }
            } catch (Exception e) {
                logger.info("createSql, 发生错误,错误原因: ", e.getMessage());
            }
        }
        return sq.validate() + " order by id desc limit " + query_limit + " offset " + query_offset;
    }


    /**
     * 该方法实现接收web前端通过body传入的参数，调用数据访问层CommonParameterRepositoryImpl 类的方法实现数据的增、删、改。
     * 通过含参数的Json对象bodyJSObj，通过method字段获取操作类型，分别是insert，update, delete
     * 1) 操作类型为 insert，新增数据
     * 2）操作类型为 update，更新数据
     * 3）操作类型为 delete，逻辑删除多条记录
     * 调用GoodsMinlineTransportRepositoryImpl 的方法
     *
     * @param client
     * @param bodyJSObj
     * @param user
     * @return
     */
    public Future<JsonObject> insertOrUpdate(PgPool client, JsonObject bodyJSObj, User user) {
        Promise<JsonObject> promise = Promise.promise();
        int user_id = user.attributes().getJsonObject("accessToken").getInteger("userId");
        String create_user = user.attributes().getJsonObject("accessToken").getString("username");
        //最后返回的json对象
        JsonObject responseJSObj = new JsonObject();

        // 验证必须传递的数字字段
        List<String> numeric_fields = Arrays.asList( "freight_amount", "goods_loss_compensation_amount", "tax_rate", "transport_management_rate");
        JsonObject numeric = this.commonRepository.verifyAllParamNumeric(bodyJSObj, numeric_fields);
        if (numeric != null) {
            promise.complete(numeric);
            return promise.future();
        }

        //获取bodyJSObj参数对象中字段的值,各字段注释请参看数据库设计文档中数据表mainline_transport的说明
        //获取操作类型字段的值
        String method = bodyJSObj.getString("method");
        logger.info("method {}", method);
        long record_id=0;
        if (method.equals("insert")){
            // 验证必须传递的非数值字段
            List<String> must_fields2 = Arrays.asList("method", "driver_name", "truck_type_id", "plate_number",
                    "mainline_route_id", "batch_pickup_plan_load_completion_id_list","legal_entity_id");
            JsonObject must2 = this.commonRepository.verifyAllParamMust(bodyJSObj, must_fields2);
            if (must2 != null) {
                promise.complete(must2);
                return promise.future();
            }
        }else {
            // 验证必须传递的非数值字段
            record_id = Long.valueOf(bodyJSObj.getString("record_id"));
            List<String> must_fields = Arrays.asList("method", "record_id", "truck_type_id", "plate_number",
                    "freight_amount", "goods_loss_compensation_amount","tax_rate","transport_management_rate");
            JsonObject must = this.commonRepository.verifyAllParamMust(bodyJSObj, must_fields);
            if (must != null) {
                promise.complete(must);
                return promise.future();
            }
        }


        long finalRecord_id = record_id;
        this.commonRepository.getCon(client).onSuccess(conn -> {
                    //4个数值
            Double freight_amount = bodyJSObj.getDouble("freight_amount");
            Double goods_loss_compensation_amount = bodyJSObj.getDouble("goods_loss_compensation_amount");
            Double tax_rate = bodyJSObj.getDouble("tax_rate");
            Long truck_type_id = Long.valueOf(bodyJSObj.getString("truck_type_id"));
            Double transport_management_rate = bodyJSObj.getDouble("transport_management_rate");
            String plate_number = bodyJSObj.getString("plate_number");
            if (method.equals("insert")) {
                Long legal_entity_id = Long.valueOf(bodyJSObj.getString("legal_entity_id"));
                String driver_name = bodyJSObj.getString("driver_name");
                Long mainline_route_id = Long.valueOf(bodyJSObj.getString("mainline_route_id"));
                //批次提货计划单有多个，分割列表字符串去掉"["和"]"
                String batch_pickup_plan_load_completion_ids = bodyJSObj.getString("batch_pickup_plan_load_completion_id_list");
                String batch_pickup_plan_load_completion_id_list = batch_pickup_plan_load_completion_ids.substring(1, batch_pickup_plan_load_completion_ids.length() - 1);
                //将分割后的批次号保存到列表当中
                List<String> batch_pickup_plan_load_completion_id_list_set = Arrays.asList(batch_pickup_plan_load_completion_id_list.split(","));

                /*
                 * 校验多个code是否合法
                 */
                //校验运输商(支付主体)code
                String legal_entity_id_sql = "select id, legal_entity_name,account_bank,account_number from payment_entity where id = $1 and is_delete = 0 limit 1";
                Future<JsonObject> legalEntityCodeFuture = this.commonRepository.queryOneJsonObjectThrow(conn, legal_entity_id_sql, Tuple.of(legal_entity_id), "insert_mainline_transport", "运输商名 操作失败");
                //校验司机名
                String driver_name_sql = "select id,account_type_id ,account_bank,receive_account_name,receive_account_number from carrier_driver where driver_name = $1 and is_delete = 0 limit 1";
                Future<JsonObject> driverNameFuture = this.commonRepository.queryOneJsonObjectThrow(conn, driver_name_sql, Tuple.of(driver_name), "insert_mainline_transport", "司机名 操作失败");
                //校验车型code
                String truck_type_id_sql = "select id, type_name from truck_type where id = $1 and is_delete = 0 limit 1";
                Future<JsonObject> truckTypeCodeFuture = this.commonRepository.queryOneJsonObjectThrow(conn, truck_type_id_sql, Tuple.of(truck_type_id), "insert_mainline_transport", "车型code 操作失败");
                //校验车牌号
                String plate_number_sql = "select plate_number from carrier_driver where plate_number = $1 and is_delete = 0 limit 1";
                Future<JsonObject> plateNumberFuture = this.commonRepository.queryOneJsonObjectThrow(conn, plate_number_sql, Tuple.of(plate_number), "insert_mainline_transport", "车牌号 操作失败");
                //校验干线运输标准线路code
                String mainline_route_id_sql = "select id,mainline_route_name from mainline_route where id = $1 and is_delete = 0 limit 1";
                Future<JsonObject> mainlineRouteCodeFuture = this.commonRepository.queryOneJsonObjectThrow(conn, mainline_route_id_sql, Tuple.of(mainline_route_id), "insert_mainline_transport", "校验干线运输标准线路code 操作失败");
                //校验批次提货计划装车完工单编码
                Future<JsonObject> batchPickupPlanLoadCompletionCodeListFuture = checkBatchPickupPlanLoadCompletionIdFuture(conn, batch_pickup_plan_load_completion_id_list);

                //根据批次提货计划装车完工单id获取预提计划比价单id，一对多,每一个JsonObject对应一条记录
                String prepare_pickup_plan_price_bid_id_sql = "select batch_pickup_plan_load_completion_id,prepare_pickup_plan_price_bid_id from batch_pickup_plan_load_completion_detail where batch_pickup_plan_load_completion_id =  any(array[" + String.join(",", batch_pickup_plan_load_completion_id_list_set) + "]) and is_delete = 0";
                Future<JsonArray> getPreparePickupPlanPriceBidIdFuture = this.commonRepository.queryJsonArray(conn, prepare_pickup_plan_price_bid_id_sql, "get_batch_pickup_plan_load_completion_detail_id");

                //根据批次提货计划装车完工单明细id从实际提货件数销售地区分配表获取比价单编号prepare_pickup_plan_price_bid_id、仓库分配件数、仓库分配重量，一对多
                String real_pickup_plan_number_allocation_sql = "select distinct prepare_pickup_plan_price_bid_id, batch_pickup_plan_load_completion_detail_id,warehouse_allocate_number,warehouse_allocate_weight from real_pickup_plan_number_allocation where batch_pickup_plan_load_completion_detail_id = " +
                        "(select id from batch_pickup_plan_load_completion_detail where batch_pickup_plan_load_completion_id =  any(array[" + String.join(",", batch_pickup_plan_load_completion_id_list_set) + "]) and is_delete = 0 limit 1) and is_delete = 0";
                Future<JsonArray> getRealPickupPlanNumberAllocationFuture = this.commonRepository.queryJsonArray(conn, real_pickup_plan_number_allocation_sql, "get_real_pickup_plan_number_allocation");

                //根据比价单编号获取销售地区,一对一[prepare_pickup_plan_price_bid_id,site_id]
                String real_site_sql = "select distinct id,site_id from prepare_pickup_plan_price_bid where id = " +
                        "(select prepare_pickup_plan_price_bid_id from batch_pickup_plan_load_completion_detail where batch_pickup_plan_load_completion_id =" +
                        "any(array[" + String.join(",", batch_pickup_plan_load_completion_id_list_set) + "]) and is_delete = 0 limit 1) and is_delete = 0";
                Future<JsonObject> getRealSiteFuture = this.commonRepository.getCodeNamePairJsonObject(conn, real_site_sql, "get_real_site_id");

                //根据mainline_route_id从mainline_freight_allocation中获取销售地区以及对应的分摊比例
                String get_site_id_freight_allocation_ratio_sql = "select site_id,freight_allocation_ratio from mainline_freight_allocation where mainline_route_id = $1 and is_delete = 0";
                Future<JsonObject> getSiteCodeFreightAllocationRatio = this.commonRepository.getIdNamePairJsonObject(conn, get_site_id_freight_allocation_ratio_sql, Tuple.of(mainline_route_id), "get_site_id_freight_allocation_ratio_sql");

                //根据prepare_pickup_plan_price_bid_id从prepare_pickup_plan_price_bid表获取goods_gather_area_id
                String get_goods_gather_area_id_sql = "select distinct id,goods_gather_area_id from prepare_pickup_plan_price_bid where id = " +
                        "(select distinct prepare_pickup_plan_price_bid_id from batch_pickup_plan_load_completion_detail where batch_pickup_plan_load_completion_id = any(array[" + String.join(",", batch_pickup_plan_load_completion_id_list_set) + "]) and is_delete=0 limit 1)";
                Future<JsonObject> getGoodsGatherAreaCode = this.commonRepository.getCodeNamePairJsonObject(conn, get_goods_gather_area_id_sql, "getGoodsGatherAreaCode");



                //判断插入的数据是否已经存在，判断依据是车牌号和干线运输标准线路名
                String mainline_transport_sql = "select count(*) as total from mainline_transport where mainline_route_id = $1 and plate_number = $2 ";
                Future<Integer> mainlineRouteCodePlateNumberFuture = this.commonRepository.getRecordsCount(conn, mainline_transport_sql, Tuple.of(mainline_route_id, plate_number), "insert_mainline_transport");

                // 并行执行多个future
                CompositeFuture.join(Arrays.asList(mainlineRouteCodePlateNumberFuture, getGoodsGatherAreaCode, getSiteCodeFreightAllocationRatio, getPreparePickupPlanPriceBidIdFuture, getRealSiteFuture, getRealPickupPlanNumberAllocationFuture, legalEntityCodeFuture, driverNameFuture, truckTypeCodeFuture, plateNumberFuture, mainlineRouteCodeFuture, batchPickupPlanLoadCompletionCodeListFuture))
                .onComplete(ar -> {
                    if (ar.succeeded()) {
                        String str =ar.result().list().get(0).toString();
                        int total_records = Integer.parseInt(str);
                        //获取到批次提货计划装车完工单编号对应的商品聚集区["batch_pickup_plan_load_completion_id":"goods_gather_area_id"],可以得到每个批次号对应的商品聚集区
                        JsonObject goods_gather_area_id_info = (JsonObject) ar.result().list().get(1);
                        JsonObject site_freight_allocation_ratio_info = (JsonObject) ar.result().list().get(2);
                        JsonArray prepare_pickup_plan_price_bid_id_info = (JsonArray) ar.result().list().get(3);
                        JsonObject prepare_pickup_plan_price_bid_id_to_site_info = (JsonObject) ar.result().list().get(4);
                        JsonArray real_pickup_plan_number_allocation_info = (JsonArray) ar.result().list().get(5);
                        JsonObject payment_entity_info=(JsonObject) ar.result().list().get(6);
                        JsonObject carrier_driver_info = (JsonObject) ar.result().list().get(7);

                        if (total_records == 0) {
                            // 新增
                            ArrayList<Future> futures = new ArrayList<>();
                            for (int i = 0; i < batch_pickup_plan_load_completion_id_list_set.size(); i++) {
                                Long batch_pickup_plan_load_completion_id = Long.parseLong(batch_pickup_plan_load_completion_id_list_set.get(i));
                                //筛选出该完工单编号下的所有预提计划比价单id
                                Set<Long> prepare_pickup_plan_price_bid_id_set = new HashSet<>();
                                for (int j = 0; j < prepare_pickup_plan_price_bid_id_info.size(); j++) {
                                    JsonObject jsonObject = prepare_pickup_plan_price_bid_id_info.getJsonObject(j);
                                    if (jsonObject.getString("batch_pickup_plan_load_completion_id").equals(batch_pickup_plan_load_completion_id.toString())) {
                                        prepare_pickup_plan_price_bid_id_set.add(Long.parseLong(jsonObject.getString("prepare_pickup_plan_price_bid_id")));
                                    }
                                }
                                //转化成list
                                ArrayList<Long> prepare_pickup_plan_price_bid_id_list = new ArrayList<>(prepare_pickup_plan_price_bid_id_set);
                                //根据预提计划比价单id,将对应的实际提货件数销售地区分配情况拿出来
                                JsonArray real_pickup_plan_number_allocation_jsonArray = new JsonArray();
                                for (int j = 0; j < real_pickup_plan_number_allocation_info.size(); j++) {
                                    JsonObject jsonObject = real_pickup_plan_number_allocation_info.getJsonObject(j);
                                    //如果该条记录属于该预提计划比价单id(也就是属于该完工单编号，则符合要求)
                                    if (prepare_pickup_plan_price_bid_id_list.contains(Long.parseLong(jsonObject.getString("prepare_pickup_plan_price_bid_id")))) {
                                        real_pickup_plan_number_allocation_jsonArray.add(jsonObject);
                                    }
                                }
                                // 创建新的jsonobject，按销售地区统计每个地区的实际提货件数、提货重量
                                JsonObject sales_pickup_number_jsonobject = new JsonObject();
                                JsonObject sales_pickup_weight_jsonobject = new JsonObject();
                                //遍历实际提货件数销售地区分配表的每一条记录，筛选出该车次完工单下的记录
                                for (int j = 0; j < real_pickup_plan_number_allocation_jsonArray.size(); j++) {
                                    JsonObject jsonObject = real_pickup_plan_number_allocation_jsonArray.getJsonObject(j);
                                    String prepare_pickup_plan_price_bid_id = jsonObject.getString("prepare_pickup_plan_price_bid_id");
                                    String site_id = prepare_pickup_plan_price_bid_id_to_site_info.getString(prepare_pickup_plan_price_bid_id);
                                    double sales_pickup_number = Double.parseDouble(jsonObject.getString("warehouse_allocate_number"));
                                    double sales_pickup_weight = Double.parseDouble(jsonObject.getString("warehouse_allocate_weight"));
                                    // 如果该销售地区不在jsonobject中插入
                                    if (sales_pickup_number_jsonobject.getString(site_id) == null || sales_pickup_number_jsonobject.getString(site_id).equals("")) {
                                        sales_pickup_number_jsonobject.put(site_id, sales_pickup_number);
                                        sales_pickup_weight_jsonobject.put(site_id, sales_pickup_weight);
                                    } else {//否则更新，在原有的基础上加
                                        sales_pickup_number_jsonobject.put(site_id, sales_pickup_number_jsonobject.getString(site_id) + sales_pickup_number);
                                        sales_pickup_weight_jsonobject.put(site_id, sales_pickup_weight_jsonobject.getString(site_id) + sales_pickup_weight);
                                    }
                                }
                                //计算每个地区的销售地区实际运费斤价的分母
                                double all_fenmu = 0;
                                for (int j = 0; j < prepare_pickup_plan_price_bid_id_list.size(); j++) {
                                    String site_id = prepare_pickup_plan_price_bid_id_to_site_info.getString(String.valueOf(prepare_pickup_plan_price_bid_id_list.get(j)));
//                                    String str1 =site_freight_allocation_ratio_info.getString(site_id);
                                    //该销售地区的运费分摊比例
                                    double freight_allocation_ratio = Double.parseDouble(site_freight_allocation_ratio_info.getString(site_id)==null ? "0":site_freight_allocation_ratio_info.getString(site_id));
                                    //该销售地区的提货重量
                                    double sales_pickup_weight = Double.parseDouble(sales_pickup_weight_jsonobject.getString(site_id));
                                    all_fenmu += freight_allocation_ratio * sales_pickup_weight;
                                }
                                //按销售地区插入新数据
                                for (int j = 0; j < prepare_pickup_plan_price_bid_id_list.size(); j++) {
                                    //供应链商品聚集区
                                    Long goods_gather_area_id = Long.parseLong(goods_gather_area_id_info.getString(String.valueOf(prepare_pickup_plan_price_bid_id_list.get(j))));
                                    //获取销售地区
                                    Long site_id = Long.parseLong(prepare_pickup_plan_price_bid_id_to_site_info.getString(String.valueOf(prepare_pickup_plan_price_bid_id_list.get(j))));
                                    //销售地区分摊比例
                                    double freight_allocation_ratio = Double.parseDouble(site_freight_allocation_ratio_info.getString(site_id.toString())==null ? "0":site_freight_allocation_ratio_info.getString(String.valueOf(site_id)));
                                    //该销售地区的提货件数
                                    double sales_pickup_number = Double.parseDouble(sales_pickup_number_jsonobject.getString(site_id.toString()));
                                    //该销售地区的提货重量
                                    double sales_pickup_weight = Double.parseDouble(sales_pickup_weight_jsonobject.getString(site_id.toString()));
                                    //应付干线运费金额
                                    double pay_mainline_freight_amount = freight_amount - goods_loss_compensation_amount;
                                    //销售地区干线运费斤价
                                    double sales_real_transport_price = pay_mainline_freight_amount / all_fenmu * freight_allocation_ratio;


                                    //生成干线运输单编号

                                    //从goods_gather_areae表中获取对应供应链商品聚集区的关键字汉拼首字母
                                    String get_goods_gather_area_pinyiin_sql = "select pinyin from goods_gather_area where id = $1 and is_delete = 0 limit 1";
                                    Future<JsonObject> getGoodsGatherAreaPinyin = this.commonRepository.queryOneJsonObject(conn, get_goods_gather_area_pinyiin_sql, Tuple.of(goods_gather_area_id), "get_goods_gather_area_pinyiin_sql", "获取供应链商品聚集区名关键字汉拼首字母 操作失败");
                                    //从site表中获取销售地区名关键字汉拼首字母
                                    String get_site_pinyiin_sql = "select pinyin from site where id = $1 and is_delete = 0 limit 1";
                                    Future<JsonObject> getSitePinyin = this.commonRepository.queryOneJsonObject(conn, get_site_pinyiin_sql, Tuple.of(site_id), "get_site_pinyiin_sql", "获取获取销售地区名关键字汉拼首字母 操作失败");
                                    //从truck_type表中获取对应车型编号
                                    String get_truck_type_pinyiin_sql = "select type_pinyin as pinyin from truck_type where id = $1 and is_delete = 0 limit 1";
                                    Future<JsonObject> getTruckTypePinyiin = this.commonRepository.queryOneJsonObject(conn, get_truck_type_pinyiin_sql, Tuple.of(truck_type_id), "get_truck_type_pinyiin_sql", "获取供应链商品聚集区名关键字汉拼首字母 操作失败");

                                    //判断插入的数据是否已经存在
                                    String count_sql = "select count(*) as total from mainline_transport where goods_gather_area_id = $1 and site_id = $2 and truck_type_id = $3 and mainline_route_id = $4 and plate_number = $5";
                                    Future<Integer> getCountFuture = this.commonRepository.getRecordsCount(conn, count_sql, Tuple.of(goods_gather_area_id, site_id, truck_type_id, mainline_route_id, plate_number), "count_sql");


                                    CompositeFuture.join(Arrays.asList(getCountFuture, getGoodsGatherAreaPinyin, getSitePinyin, getTruckTypePinyiin))
                                            .onComplete(ar2 -> {
                                                if (ar2.succeeded()) {
                                                    int total_records2 = Integer.valueOf(ar2.result().list().get(0).toString());
                                                    //顺序号
                                                    String number = String.valueOf(101 + total_records2);

                                                    //供应链商品聚集区名关键字汉拼首字母
                                                    JsonObject goods_value = (JsonObject) ar2.result().list().get(1);
                                                    String goods_gather_area_pinyiin = null;
                                                    if (goods_value.getValue("pinyin") != null && !goods_value.getValue("pinyin").equals(""))
                                                        goods_gather_area_pinyiin = goods_value.getString("pinyin");
                                                    //销售地区名关键字汉拼首字母
                                                    goods_value = (JsonObject) ar2.result().list().get(2);
                                                    String site_pinyiin = null;
                                                    if (goods_value.getValue("pinyin") != null && !goods_value.getValue("pinyin").equals(""))
                                                        site_pinyiin = goods_value.getString("pinyin");
                                                    //车型编号
                                                    goods_value = (JsonObject) ar2.result().list().get(3);
                                                    String truck_type_pinyiin = null;
                                                    if (goods_value.getValue("pinyin") != null && !goods_value.getValue("pinyin").equals(""))
                                                        truck_type_pinyiin = goods_value.getString("pinyin");
                                                    //生成编号
                                                    Calendar calendar = Calendar.getInstance();
                                                    int year = calendar.get(Calendar.YEAR);
                                                    int month = calendar.get(Calendar.MONTH) + 1; // 月份从0开始，所以要加1
                                                    int day = calendar.get(Calendar.DAY_OF_MONTH);
                                                    String a1 = String.valueOf(year);
                                                    String a2="";
                                                    if (month<10){
                                                        a2 = "0"+String.valueOf(month);
                                                    }else {
                                                        a2 = String.valueOf(month);
                                                    }
                                                    String a3="";
                                                    if (month<10){
                                                        a3 = "0"+String.valueOf(day);
                                                    }else {
                                                        a3 = String.valueOf(day);
                                                    }
                                                    String date =a1.substring(2,3)+a2+a3;
                                                    String mainline_transport_document_code ="GXZF-"+goods_gather_area_pinyiin+"-"+site_pinyiin+"-"+truck_type_pinyiin+"AA-"+date+number;



                                                    //插入数据
                                                    String insert_mainline_transport_sql = "INSERT INTO mainline_transport(legal_entity_id,driver_name,truck_type_id,plate_number," +
                                                            "mainline_transport_document_code,mainline_route_id, freight_amount, goods_gather_area_id,site_id,freight_allocation_ratio," +
                                                            "batch_pickup_plan_load_completion_id,sales_pickup_number,sales_pickup_weight,sales_real_transport_price,goods_loss_compensation_amount,tax_rate,transport_management_rate,create_user)" +
                                                            " VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11,$12,$13,$14,$15,$16,$17,$18)  RETURNING id";
                                                    Future future1 = this.commonRepository.insertOrUpdate(conn, insert_mainline_transport_sql, Tuple.of(legal_entity_id, driver_name, truck_type_id, plate_number,
                                                            mainline_transport_document_code, mainline_route_id, freight_amount, goods_gather_area_id, site_id, freight_allocation_ratio, batch_pickup_plan_load_completion_id,
                                                            sales_pickup_number, sales_pickup_weight, sales_real_transport_price, goods_loss_compensation_amount, tax_rate, transport_management_rate, create_user), "insert_mainline_transport", "新增干线运费支付管理");
                                                    //一对一自动生成干线运费支付单

                                                    //生成干线运费支付单编号

                                                    Set<String> set = Pinyin4jUtil.getPinyin(driver_name,false);
                                                    String pin ="";
                                                    for (String ent : set){
                                                        pin=pin+ent;
                                                    }
                                                    String mainline_freight_payment_code = "GXZF-"+pin+"-"+date+"-"+mainline_transport_document_code;
                                                    int carrier_driver_id=carrier_driver_info.getInteger("id");
                                                    int account_type_id2 = Integer.parseInt(payment_entity_info.getString("account_type_id"));
                                                    String account_bank2 =carrier_driver_info.getString("account_bank");
                                                    String receive_account_name2 =carrier_driver_info.getString("receive_account_name");
                                                    String receive_account_number2 =carrier_driver_info.getString("receive_account_number");
                                                    int legal_entity_id2 =payment_entity_info.getInteger("id");
                                                    String pay_account_number2=payment_entity_info.getString("pay_account_number");
                                                    String pay_account_bank2 =payment_entity_info.getString("pay_account_bank");
                                                    //插入数据
                                                    String insert_mainline_freight_payment_sql = "INSERT INTO mainline_freight_payment(mainline_freight_payment_code,mainline_transport_id,create_user,carrier_driver_id,update_user, " +
                                                            "account_type_id,account_bank,receive_account_name ,receive_account_number, legal_entity_id,pay_account_number, pay_account_bank)" +
                                                            " VALUES ($1, $2, $3, $4,$5,$6,$7,$8,$9,$10,$11,$12)  RETURNING id";
                                                    Future future2 = this.commonRepository.insertOrUpdate(conn, insert_mainline_freight_payment_sql, Tuple.of(mainline_freight_payment_code,mainline_transport_document_code, create_user,carrier_driver_id,create_user,
                                                            account_type_id2,account_bank2,receive_account_name2,receive_account_number2,legal_entity_id2,pay_account_number2,pay_account_bank2), "insert_mainline_transport", "新增干线运费支付管理");

                                                    //一对一自动生成运输管理收入单
                                                    //插入数据
                                                    String transportation_revenue_code_code="YSSR-"+mainline_freight_payment_code;
                                                    String insert_transport_revenue_sql = "INSERT INTO transport_revenue(transportation_revenue_code_code,mainline_transport_id,create_user,pay_account_number,pay_account_bank,legal_entity_id)" +
                                                            " VALUES ($1, $2, $3,$4,$5,$6)  RETURNING id";
                                                    long user10 = Long.parseLong(create_user);
                                                    Future future3 = this.commonRepository.insertOrUpdate(conn, insert_transport_revenue_sql, Tuple.of(transportation_revenue_code_code,mainline_transport_document_code, user10
                                                    ,pay_account_number2,pay_account_bank2,legal_entity_id2), "insert_transport_revenue", "新增运输管理收入单");


                                                    futures.add(future1);
                                                    futures.add(future2);
                                                    futures.add(future3);
                                                } else {
                                                    promise.fail(CommonUtils.createExceptionJsonObjectStr(ar2, "GenerateCode", "生成干线支付单编号发生错误"));
                                                }
                                            }).onFailure(err -> promise.fail(CommonUtils.createExceptionJsonObjectStr(err, "getGoodsPriceBidParameterRecords", "数据库连接失败")));
                                }
                                //将生成干线运费支付单的完工单设置成已生成状态

                            }
                            //并行执行多个future
                            CompositeFuture.join(futures).onComplete(ar2 -> {
                                if (ar2.succeeded()) {
                                    JsonObject resultJSObj  = new JsonObject();
                                    resultJSObj.put("code",200).put("msg","新增数据成功!");
                                    promise.complete(resultJSObj);
                                } else {
                                    logger.info("插入记录数据发生错误: {}", ar2.cause().getMessage());
                                    promise.complete(CommonUtils.createExceptionJsonObject(ar2, "insertMinlineTransport", "新增数据发生错误"));
                                }
                            });
                        }
                        else {
                            logger.info("插入记录数据已经存在");
                                    promise.complete(CommonUtils.createExceptionJsonObject(ar, "insertMinlineTransport", "插入记录数据已经存在"));
                        }
//                        else {
//                            // 更新
//                            ArrayList<Future> futures = new ArrayList<>();
//                            for (int i = 0; i < batch_pickup_plan_load_completion_id_list_set.size(); i++) {
//                                String batch_pickup_plan_load_completion_id = batch_pickup_plan_load_completion_id_list_set.get(i);
//                                String goods_gather_area_id = goods_gather_area_id_info.getString(batch_pickup_plan_load_completion_id);
//
//                                for (int j = 0; j < site_id_list.size(); j++) {
//                                    //获取对应的商品聚集区、销售地、分担比例
//                                    String site_id = site_id_list.get(j);
//                                    Double freight_allocation_ratio = freight_allocation_ratio_list.get(j);
//                                    String mainline_transport_document_code = "GXYS";
//                                    //String mainline_transport_document_code = GenerateCode(client, "GXYS", goods_gather_area_id, site_id, truck_type_id, mainline_route_id, plate_number);
//                                    //插入数据
//                                    String update_sql = "update mainline_transport set legal_entity_id = $1, driver_name = $2,truck_type_id=$3,plate_number = $4, mainline_transport_document_code = $5," +
//                                    " mainline_route_id = $6, freight_amount = $7,goods_gather_area_id= $8,site_id= $9,freight_allocation_ratio= $10,batch_pickup_plan_load_completion_id = $11," +
//                                    "goods_loss_compensation_amount = $12,tax_rate = $13,transport_management_rate = $14,update_user = $15 WHERE plate_number = $16 and mainline_route_id = $17 RETURNING id";
//                                    Future future = this.commonRepository.insertOrUpdate(conn, update_sql, Tuple.of(legal_entity_id, driver_name, truck_type_id, plate_number,
//                                    mainline_transport_document_code, mainline_route_id, freight_amount, goods_gather_area_id, site_id, freight_allocation_ratio,
//                                    batch_pickup_plan_load_completion_id, goods_loss_compensation_amount, tax_rate, transport_management_rate, create_user, plate_number, mainline_route_id), "update_mainline_transport", "更新干线运费支付管理");
//                                    futures.add(future);
//                                }
//                            }
//                            //并行执行多个future
//                            CompositeFuture.join(futures).onComplete(ar2 -> {
//                                if (ar2.succeeded()) {
//                                    promise.complete(CommonUtils.createExceptionJsonObject(ar2, "insertMinlineTransport", "新增数据发生成功!"));
//                                } else {
//                                    logger.info("插入记录数据发生错误: {}", ar2.cause().getMessage());
//                                    promise.complete(CommonUtils.createExceptionJsonObject(ar2, "insertMinlineTransport", "新增数据发生错误"));
//                                }
//                            });
//                        }
                    } else {
                        promise.fail(CommonUtils.createExceptionJsonObjectStr(ar, "insertMinlineTransport", "新增数据发生错误"));
                    }
                });
            } else if (method.equals("update")) {
                // 并行执行多个future
                CompositeFuture.join(Arrays.asList())
                .onComplete(ar -> {
                    if (ar.succeeded()) {
                        String update_sql = "UPDATE mainline_transport SET truck_type_id= $1,plate_number= $2,freight_amount= $3, goods_loss_compensation_amount = $4,tax_rate = $5,transport_management_rate = $6, update_user=$7 WHERE id = $8 RETURNING id";
                        this.commonRepository.insertOrUpdate(conn, update_sql, Tuple.of(truck_type_id, plate_number, freight_amount, goods_loss_compensation_amount, tax_rate, transport_management_rate, create_user, finalRecord_id), "update_mainline_transport", "更新干线运费支付管理")
                        .onSuccess(resultJSObj -> {
                            //写入操作日志
                            this.commonRepository.writePlatformLog(conn, user_id, "mainline_transport/update", bodyJSObj, resultJSObj, "")
                            .onComplete(ar3 -> {
                                promise.complete(resultJSObj);
                            });
                        }).onFailure(err -> promise.fail(CommonUtils.createExceptionJsonObjectStr(err, "update_mainline_transport", "更新干线运费支付管理")));
                    } else {
                        promise.fail(CommonUtils.createExceptionJsonObjectStr(ar, "updateMinlineTransport", "更新数据发生错误"));
                    }
                });
            }
        }).
        onFailure(err -> promise.fail(CommonUtils.createExceptionJsonObjectStr(err, "insertorupdateMinlineTransport", "数据库连接失败")));
        return promise.future();
    }


//    /***
//     * 根据字段生成干线运输单编号
//     * @param type
//     * @param goods_gather_area_id
//     * @param site_id
//     * @param truck_type_id
//     * @return
//     */
//    private String GenerateMainlineTransportDocumentCode(PgPool client, String type, Long
//    goods_gather_area_id, Long site_id, Long truck_type_id, Long mainline_route_id, String plate_number) {
//        JsonObject Code = new JsonObject();
//        String str ="";
//        Promise<JsonObject> promise = Promise.promise();
//        this.commonRepository.getCon(client).onSuccess(conn -> {
//            //从goods_gather_areae表中获取对应供应链商品聚集区的关键字汉拼首字母
//            String get_goods_gather_area_pinyiin_sql = "select pinyin_order from goods_gather_area where id = $1 and is_delete = 0 limit 1";
//            Future<JsonObject> getGoodsGatherAreaPinyin = this.commonRepository.queryOneJsonObject(conn, get_goods_gather_area_pinyiin_sql, Tuple.of(goods_gather_area_id), "get_goods_gather_area_pinyiin_sql", "获取供应链商品聚集区名关键字汉拼首字母 操作失败");
//            //从site表中获取销售地区名关键字汉拼首字母
//            String get_site_pinyiin_sql = "select pinyin_order from site where id = $1 and is_delete = 0 limit 1";
//            Future<JsonObject> getSitePinyin = this.commonRepository.queryOneJsonObject(conn, get_site_pinyiin_sql, Tuple.of(site_id), "get_site_pinyiin_sql", "获取获取销售地区名关键字汉拼首字母 操作失败");
//            //从truck_type表中获取对应车型编号
//            String get_truck_type_pinyiin_sql = "select type_pinyin from truck_type where id = $1 and is_delete = 0 limit 1";
//            Future<JsonObject> getTruckTypePinyiin = this.commonRepository.queryOneJsonObject(conn, get_truck_type_pinyiin_sql, Tuple.of(truck_type_id), "get_truck_type_pinyiin_sql", "获取供应链商品聚集区名关键字汉拼首字母 操作失败");
//
//            //判断插入的数据是否已经存在
//            String count_sql = "select count(*) as total from mainline_transport where goods_gather_area_id = $1 and site_id = $2 and truck_type_id = $3 and mainline_route_id = $4 and plate_number = $5";
//            Future<Integer> getCountFuture = this.commonRepository.getRecordsCount(conn, count_sql, Tuple.of(goods_gather_area_id, site_id, truck_type_id, mainline_route_id, plate_number), "count_sql");
//
//            CompositeFuture.join(Arrays.asList(getCountFuture, getGoodsGatherAreaPinyin, getSitePinyin, getTruckTypePinyiin))
//            .onComplete(ar -> {
//                if (ar.succeeded()) {
//                    int total_records = Integer.valueOf(ar.result().list().get(0).toString());
//                    //顺序号
//                    String number = String.valueOf(101 + total_records);
//
//                    //供应链商品聚集区名关键字汉拼首字母
//                    JsonObject goods_value = (JsonObject) ar.result().list().get(1);
//                    String goods_gather_area_pinyiin = null;
//                    if (goods_value.getValue("pinyin") != null && !goods_value.getValue("pinyin").equals(""))
//                        goods_gather_area_pinyiin = goods_value.getString("pinyin");
//                    //销售地区名关键字汉拼首字母
//                    goods_value = (JsonObject) ar.result().list().get(2);
//                    String site_pinyiin = null;
//                    if (goods_value.getValue("pinyin") != null && !goods_value.getValue("pinyin").equals(""))
//                        site_pinyiin = goods_value.getString("pinyin");
//                    //车型编号
//                    goods_value = (JsonObject) ar.result().list().get(3);
//                    String truck_type_pinyiin = null;
//                    if (goods_value.getValue("pinyin") != null && !goods_value.getValue("pinyin").equals(""))
//                        truck_type_pinyiin = goods_value.getString("pinyin");
//                    //生成编号
//                    Calendar calendar = Calendar.getInstance();
//                    int year = calendar.get(Calendar.YEAR);
//                    int month = calendar.get(Calendar.MONTH) + 1; // 月份从0开始，所以要加1
//                    int day = calendar.get(Calendar.DAY_OF_MONTH);
//                    String a1 = String.valueOf(year);
//                    String a2="";
//                    if (month<10){
//                        a2 = "0"+String.valueOf(month);
//                    }else {
//                        a2 = String.valueOf(month);
//                    }
//                    String a3="";
//                    if (month<10){
//                         a3 = "0"+String.valueOf(day);
//                    }else {
//                         a3 = String.valueOf(day);
//                    }
//                    String date =a1.substring(2,3)+a2+a3;
//                    str =type+"-"+goods_gather_area_pinyiin+"-"+site_pinyiin+"-"+truck_type_pinyiin+"-"+date+number;
//                    return str;
//                } else {
//                    promise.fail(CommonUtils.createExceptionJsonObjectStr(ar, "GenerateCode", "生成干线支付单编号发生错误"));
//                }
//            }).onFailure(err -> promise.fail(CommonUtils.createExceptionJsonObjectStr(err, "getGoodsPriceBidParameterRecords", "数据库连接失败")));
//        }).onFailure(err -> promise.fail(CommonUtils.createExceptionJsonObjectStr(err, "getGoodsPriceBidParameterRecords", "数据库连接失败")));
//
//    }

    /**
     * 校验批次提货计划装车完工单编号，比较复杂，需要对多个批次提货计划装车完工单编号进行校验，最后组装批次提货计划装车完工单编号
     *
     * @param conn
     * @param batch_pickup_plan_load_completion_id
     * @return
     */
    public Future<JsonObject> checkBatchPickupPlanLoadCompletionIdFuture(SqlConnection conn, String
    batch_pickup_plan_load_completion_id) {
        Promise<JsonObject> promise = Promise.promise();
        JsonObject resultJSObj = new JsonObject();
        resultJSObj.put("msg", "批次提货计划装车完工单编号校验: ");

        //第二层级的多个 futures
        @SuppressWarnings("rawtypes")
        List<Future> futures = new ArrayList<Future>();
        logger.info(batch_pickup_plan_load_completion_id);
        //拆分成列表
        String[] batch_pickup_plan_load_completion_ids = batch_pickup_plan_load_completion_id.split(",");
        logger.info("batch_pickup_plan_load_completion_id1: " + batch_pickup_plan_load_completion_ids[0]);
        List<String> batch_pickup_plan_load_completion_id_list = Arrays.asList(batch_pickup_plan_load_completion_ids);
        logger.info("batch_pickup_plan_load_completion_id_list: " + batch_pickup_plan_load_completion_id_list);

        batch_pickup_plan_load_completion_id_list.forEach(batch_pickup_plan_load_completion_id_item -> {
            Future<JsonObject> checkSingleBatchPickupPlanLoadCompletionCodeFuture = checkSingleBatchPickupPlanLoadCompletionCodeFuture(conn, batch_pickup_plan_load_completion_id_item);
            futures.add(checkSingleBatchPickupPlanLoadCompletionCodeFuture);
        });
        CompositeFuture.join(futures).onComplete(ar2 -> {
            if (ar2.succeeded()) {
                JsonObject check_result = new JsonObject();
                List<String> batch_pickup_plan_ids = new ArrayList<String>();
                boolean result_valid = true;
                for (int i = 0; i < futures.size(); i++) {
                    check_result = (JsonObject) ar2.result().list().get(i);
                    if (check_result.getInteger("code") == -1) {
                        result_valid = false;
                    } else {
                        batch_pickup_plan_ids.add(check_result.getString("data"));
                    }
                }
                if (result_valid) {
                    resultJSObj.put("code", 0).put("data", batch_pickup_plan_ids.toString());
                } else {
                    resultJSObj.put("code", -1).put("data", "").put("msg", batch_pickup_plan_load_completion_id + "不合法");
                }
            } else {
                resultJSObj.put("code", -1).put("data", "").put("msg", batch_pickup_plan_load_completion_id + "不合法");
            }
            promise.complete(resultJSObj);
        });
        return promise.future();
    }

    public Future<JsonObject> checkSingleBatchPickupPlanLoadCompletionCodeFuture(SqlConnection conn, String
    batch_pickup_plan_load_completion_id) {
        Promise<JsonObject> promise = Promise.promise();
        JsonObject resultJSObj = new JsonObject();
        String sql = "select distinct batch_pickup_plan_id from batch_pickup_plan_load_completion where  id = '" + Long.parseLong(batch_pickup_plan_load_completion_id) + "'";
        this.commonRepository.commonSelectRowsetBySql(conn, sql)
        .onSuccess(rows -> {
            if (rows.rowCount() > 0) {
                String[] temp_value = new String[1];
                rows.forEach(item -> {
                    temp_value[0] = String.valueOf(item.getLong("batch_pickup_plan_id"));
                });
                resultJSObj.put("code", 0).put("data", temp_value[0]);
            } else {
                resultJSObj.put("code", -1).put("data", "");
            }
            promise.complete(resultJSObj);
        })
        .onFailure(throwable -> {
            logger.info("in checkSingleBatchPickupPlanLoadCompletionCodeFuture: " + throwable.toString());
            resultJSObj.put("code", -1).put("msg", throwable.toString());
            promise.complete(resultJSObj);
        });
        return promise.future();
    }
}